#include "sqlite3_api.h"


/* 打开/创建数据库 */
int open_db(db_t *sqt)
{
	if( SQLITE_OK != sqlite3_open( sqt->path, &sqt->db ) ){
		puts(sqlite3_errmsg(sqt->db));
		return -1;
	}
	
	return 0;
}


/* 创建表 */
int table_create(db_t *sqt)
{
	int n, i, ret;
	int row = 0, count = 0;
	char *errmsg;
	char sql_temp[50];
	char sql[200];

	if(TABLE_NAX <= TABLE_NUM){
		return -1;
	}

	for(n = 1; n < TABLE_NUM; n++){
		for(i = 0; strcmp(sqt->tables[n]->fields[i].name, "**"); i++){
			row++;
		}
		sprintf(sql, "create table");
		sprintf(sql_temp, " %s(", sqt->tables[n]->tableName);
		strcat(sql,sql_temp);
		for(i = 0; i < row; i++){
			if(i == row -1){
				sprintf(sql_temp, "%s %s %s);", sqt->tables[n]->fields[i].name,sqt->tables[n]->fields[i].type,sqt->tables[n]->fields[i].other);	
			}else{
				sprintf(sql_temp, "%s %s %s,", sqt->tables[n]->fields[i].name,sqt->tables[n]->fields[i].type,sqt->tables[n]->fields[i].other);		
			}
			strcat(sql,sql_temp);
		}
		printf("table_create()SQL:%s\n", sql);

		ret = sqlite3_exec(sqt->db, sql,NULL, NULL, &errmsg );
		if(SQLITE_OK != ret){
			printf("failt to table_create():%s\n", errmsg);
			return -1;
		}
		row = 0;
		count++;
	}
	return count;
}

/* 需要插入记录的各字段的值写在 table_t 的 value中*/
int record_insert(db_t *sqt, int tabindex)
{
	int i,ret;
	int row = 0;
	char *errmsg;
	char sql_temp[50];
	char sql[200];
	
	puts("*******1*******");
	for(i = 0; strcmp(sqt->tables[tabindex]->fields[i].name, "**"); i++){
		row++;
	}
	
	sprintf(sql, "insert into %s(", sqt->tables[tabindex]->tableName);
	for(i = 1; i < row; i++){
		(i == (row-1)) ? sprintf(sql_temp, " %s) values (", sqt->tables[tabindex]->fields[i].name) : sprintf(sql_temp, " %s,", sqt->tables[tabindex]->fields[i].name);
		strcat(sql, sql_temp);
	}
	for(i = 1; i < row; i++){
		if( !strcmp(sqt->tables[tabindex]->fields[i].type, TYPE_INT) ){
			(i == (row-1)) ? sprintf(sql_temp, " %s);", sqt->tables[tabindex]->fields[i].value) : sprintf(sql_temp, " %s,", sqt->tables[tabindex]->fields[i].value);
		}else if( !strcmp(sqt->tables[tabindex]->fields[i].type, TYPE_TEXT) ){
			(i == (row-1)) ? sprintf(sql_temp, " '%s');", sqt->tables[tabindex]->fields[i].value) : sprintf(sql_temp, " '%s',", sqt->tables[tabindex]->fields[i].value);
		}
		strcat(sql, sql_temp);
	}
	printf("insert_record()SQL:%s\n", sql);  ///////////////
	ret = sqlite3_exec(sqt->db, sql,NULL, NULL, &errmsg );
	if(SQLITE_OK != ret){
		printf("failt to record_insert(): %s\n", errmsg);  /////////////
		return -1;
	}
	return 1;
}

/* sql语句处理 */
int sql_handle(db_t *sqt, int tabindex, char *sql, int mode, int limit)
{
	int i;
	int flag = 0, row = 0;
	char sql_temp[50];
	char *chp;

	for(i = 0; strcmp(sqt->tables[tabindex]->fields[i].name, "**"); i++){
		row++;
	}

	for(i = 0; i < row; i++){
		if(sqt->tables[tabindex]->fields[i].flag == RETRIEVEALL){
			flag = RETRIEVEALL;
			break;
		}else if(sqt->tables[tabindex]->fields[i].flag == RETRIEVE){
			if( !strcmp(sqt->tables[tabindex]->fields[i].type, TYPE_INT) ){
				sprintf(sql_temp, " %s=%s ", sqt->tables[tabindex]->fields[i].name, sqt->tables[tabindex]->fields[i].value);
			}else if(!strcmp(sqt->tables[tabindex]->fields[i].type, TYPE_TEXT ) ){
				sprintf(sql_temp, " %s='%s' ", sqt->tables[tabindex]->fields[i].name, sqt->tables[tabindex]->fields[i].value);
			}
			flag = RETRIEVE;
			strcat(sql, sql_temp);
			break;
		}else if(sqt->tables[tabindex]->fields[i].flag == RETRIEVEAND){
			if(!strcmp(sqt->tables[tabindex]->fields[i].type, TYPE_INT)){
				sprintf(sql_temp, " %s=%s and", sqt->tables[tabindex]->fields[i].name, sqt->tables[tabindex]->fields[i].value);
			}else if(!strcmp(sqt->tables[tabindex]->fields[i].type, TYPE_TEXT ) ){
				sprintf(sql_temp, " %s='%s' and", sqt->tables[tabindex]->fields[i].name, sqt->tables[tabindex]->fields[i].value);
			}
			flag = RETRIEVEAND;
			strcat(sql, sql_temp);
		}else if(sqt->tables[tabindex]->fields[i].flag == RETRIEVEOR){
			if(!strcmp(sqt->tables[tabindex]->fields[i].type, TYPE_INT)){
				sprintf(sql_temp, " %s=%s or", sqt->tables[tabindex]->fields[i].name, sqt->tables[tabindex]->fields[i].value);
			}else if(!strcmp(sqt->tables[tabindex]->fields[i].type, TYPE_TEXT ) ){
				sprintf(sql_temp, " %s='%s' or",sqt->tables[tabindex]->fields[i].name, sqt->tables[tabindex]->fields[i].value);
			}
			flag = RETRIEVEOR;
			strcat(sql, sql_temp);
		}
		(mode == FLAG_CLEAN) ? sqt->tables[tabindex]->fields[i].flag = INVALID: FLAG_RETAUN ;
		if(flag == RETRIEVE)
			break;
	}

	if(flag == RETRIEVE){
		goto HANDLE;
	}else if(flag == RETRIEVEALL){
		if((chp = strstr(sql, "where"))){
			strcpy(chp, "\0");
		}
		goto HANDLE;
	}else if( flag != RETRIEVE && ((chp = strstr(sql, "and")) || (chp = strstr(sql, " or"))) ){
		*(sql+strlen(sql)-3) = '\0';
	}else{
		return -1;
	}

HANDLE:
	if(limit > 0 ){
		sprintf( sql+strlen(sql), "limit %d", limit);
	}

	return 0;
}

/* 统计 */
int item_callback(void*arg,int ncolumn,char**f_value,char**f_name)
{
	(*(int *)arg)++;
	
	return 0;
}
int item_count(db_t *sqt, int tabindex, int limit)
{
	char sql[200];
	int ret ;
	int items = 0;
	char *errmsg;
	
	/* 统计查询的记录条数 */
	sprintf(sql, "select * from %s where ", sqt->tables[tabindex]->tableName);
	sql_handle(sqt, tabindex , sql, FLAG_RETAUN, limit);
	
	ret = sqlite3_exec(sqt->db, sql, item_callback, (void *)&items, &errmsg );
	if(SQLITE_OK != ret){
		printf("failt to item_count(): %s\n", errmsg);
		return -1;
	}
	return items;
}


/* 待检索字段 fields 的 flag 可设为 RETRIEVEAND/RETRIEVEOR/RETRIEVE , 筛选值 写在对应 fields 的 value域; */
int record_delete(db_t *sqt, int tabindex,int limit )
{
	int ret, items = 0;
	char *errmsg;
	char sql[200];
	
	items = item_count(sqt, tabindex, 0);
	
	/* 删除记录 */
	sprintf(sql, "delete from %s where ", sqt->tables[tabindex]->tableName);
	sql_handle(sqt, tabindex , sql, FLAG_CLEAN, limit );
	
	//printf("record_delete()SQL:%s\n", sql);  //////////
	ret = sqlite3_exec(sqt->db, sql,NULL, NULL, &errmsg );
	if(SQLITE_OK != ret){
		//printf("failt to record_delete(): %s\n", errmsg);
		return -1;
	}
	
	return items;
}


int recordone_delete(db_t *sqt, int tabindex)
{
	int ret, items = 0;
	char *errmsg;
	char sql[200];


	sprintf(sql, "select * from %s where id=(select min(id) from %s)", \
			sqt->tables[tabindex]->tableName,sqt->tables[tabindex]->tableName);
	ret = sqlite3_exec(sqt->db, sql,item_callback, (void *)&items, &errmsg );
	if(SQLITE_OK != ret){
		return -1;
	};

	sprintf(sql, "delete from %s where id=(select min(id) from %s)", \
			sqt->tables[tabindex]->tableName,sqt->tables[tabindex]->tableName);
	//printf("recordone_delete()SQL:%s\n", sql);  //////////
	ret = sqlite3_exec(sqt->db, sql,NULL, NULL, &errmsg );
	if(SQLITE_OK != ret){
		//printf("failt to record_delete(): %s\n", errmsg);
		return -1;
	};
	return items;
}
int get_real_time(char *times)
{
	time_t t = time(NULL);
	struct tm *ts = localtime(&t);
	if(ts == NULL){
		strcpy(times ,"null time");
		return -1;
	}
	sprintf(times, "%02d-%02d-%02d %02d:%02d:%02d", ts->tm_year+1900, ts->tm_mon +1, ts->tm_mday,\
			ts->tm_hour, ts->tm_min, ts->tm_sec);
	return 0;
}

/* 
	待检索字段 fields 的 flag 可设为 RETRIEVEAND/RETRIEVEOR/RETRIEVE , 筛选值 写在对应 fields 的 value域;
	将要更新字段 fields 的 flag 均设为 SELECTED, 需要更改的新值写在对应 fields 的 value域;
*/
int record_update(db_t *sqt, int tabindex)
{
	int ret;
	int items = 0;
	char *errmsg;
	char sql[200];
	char time_temp[25];
	
	sprintf(sql, "select * from %s  where id=(select min(id) from %s where state='%s')", \
			sqt->tables[tabindex]->tableName,sqt->tables[tabindex]->tableName , STATE_IN);
	ret = sqlite3_exec(sqt->db, sql,item_callback, (void *)&items, &errmsg );
	if(SQLITE_OK != ret){
		return -1;
	};

	get_real_time(time_temp);
	sprintf(sql, "update %s set state='%s',time='%s' where id=(select min(id) from %s where state='%s')", \
			sqt->tables[tabindex]->tableName,\
			STATE_OUT,time_temp,sqt->tables[tabindex]->tableName , STATE_IN);
	//printf("record_update()SQL:%s\n", sql);  //////////
	ret = sqlite3_exec(sqt->db, sql,NULL, NULL, &errmsg );
	if(SQLITE_OK != ret){
		//printf("failt to record_update(): %s\n", errmsg);
		return -1;
	};
	
	return items;
}

/* 待检索字段 fields 的 flag 可设为 RETRIEVEAND/RETRIEVEOR/RETRIEVE , 筛选值 写在对应 fields 的 value域 */
int query_callback(void *arg,int ncolumn,char **f_value,char **f_name)
{
	int i = 0;
	(*(int *)arg)++;
	if((*(int *)arg) == 1){
		for(i = 0; i < ncolumn; i++){
			printf("%-20s", f_name[i]);
		}
		putchar('\n');
	}
	
	for(i = 0; i < ncolumn; i++){
		printf("%-20s", f_value[i]);
	}
	putchar('\n');
	return 0;
}

int record_query(db_t *sqt, int tabindex, int limit)
{
	int ret;
	int items = 0 ;
	char *errmsg;
	char sql[200];
	
	//items = item_count(sqt, tabindex);
	
	sprintf(sql, "select * from %s where ", sqt->tables[tabindex]->tableName);
	sql_handle(sqt, tabindex, sql, FLAG_CLEAN, limit);
	
	//printf("record_query()SQL:%s\n", sql);   ////////////
	ret = sqlite3_exec(sqt->db, sql, query_callback, (void *)&items, &errmsg );
	if(SQLITE_OK != ret){
		//printf("failt to record_query(): %s\n", errmsg);
		return -1;
	}
	
	return items;
}



int new_callback(void *arg,int ncolumn,char **f_value,char **f_name)
{
	
	printf("<table width=\"100%\"><tr>");
	int i = 0;
	for(i = 0; i < ncolumn; i++){
		if(1 != i)
			printf("<td text-align:center >%10s</td>", f_name[i]);
	}
	printf("</tr><tr>");
	for(i = 0; i < ncolumn; i++){
		if(1 != i)
			printf("<td text-align:center>%10s</td>", f_value[i]);
	}
	printf("</tr></table>");
	return 0;
}
int recordone_new(db_t *sqt, int tabindex)
{
	int ret, items = 0;
	char *errmsg;
	char sql[200];

	sprintf(sql, "select * from %s where id=(select max(id) from %s)", \
			sqt->tables[tabindex]->tableName,sqt->tables[tabindex]->tableName);
	ret = sqlite3_exec(sqt->db, sql,new_callback, NULL, &errmsg );
	if(SQLITE_OK != ret){
		return -1;
	};
	return items;
}


int add_goods( db_t *sqt, sendEnv *anamsg)
{
	/* 0-打开/创建库 */
	if(open_db( sqt ) < 0){
		printf("fail to open_db()\n");
		return -1;
	}
	//信息表----插入记录
	strcpy(sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1INFO_no].value, anamsg->goods_info);
	strcpy(sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1INFO_name].value, "货物1");
	strcpy(sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1INFO_state].value, STATE_IN);
	get_real_time(sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1INFO_time].value);
	printf("insert info:%d\n",record_insert(sqt, STORAGE1INFO_NO));
	//入库表----插入记录:wa
	strcpy(sqt->tables[STORAGE1IN_NO]->fields[STORAGE1IN_no].value, anamsg->goods_info);
	strcpy(sqt->tables[STORAGE1IN_NO]->fields[STORAGE1IN_name].value, "货物1");
	get_real_time(sqt->tables[STORAGE1IN_NO]->fields[STORAGE1IN_time].value);
	printf("insert in:%d\n",record_insert(sqt, STORAGE1IN_NO));
#if 1
	/* 查询记录 */
	//用于检索的字段的 flag域 可设为 RETRIEVEAND/RETRIEVEOR/RETRIEVE/RETRIEVEALL;筛选值 写在对应 字段 的 value 域;
	puts("**********************STORAGE1INFO**********************");
	sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1IN_name].flag = RETRIEVEALL;
	strcpy(sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1INFO_name].value, "货物1" );
	printf("find info:%d\n",record_query(sqt, STORAGE1INFO_NO, 0));

	puts("**********************STORAGE1IN**********************");
	sqt->tables[STORAGE1IN_NO]->fields[STORAGE1IN_name].flag = RETRIEVEALL;
	strcpy(sqt->tables[STORAGE1IN_NO]->fields[STORAGE1INFO_name].value, "货物1" );
	printf("find 1in:%d\n",record_query(sqt, STORAGE1IN_NO, 0));

	puts("**********************STORAGE1OUT**********************");
	sqt->tables[STORAGE1OUT_NO]->fields[STORAGE1OUT_name].flag = RETRIEVEALL;
	strcpy(sqt->tables[STORAGE1OUT_NO]->fields[STORAGE1OUT_name].value, "货物1" );
	printf("find 1out:%d\n",record_query(sqt, STORAGE1OUT_NO, 0));
	
	puts("**********************ENVI1****************************");
	sqt->tables[ENVI1_NO]->fields[ENVI1_temp].flag = RETRIEVEALL;
	printf("find envi1:%d\n",record_query(sqt, ENVI1_NO, 0));
#endif 
	return 0;
}

int del_goods(db_t *sqt, sendEnv *anamsg)
{
	int ret;
	/* 0-打开/创建库 */
	if(open_db( sqt ) < 0){
		printf("fail to open_db()\n");
		return -1;
	}
	//入库表----删除记录
	if((ret = recordone_delete(sqt, STORAGE1IN_NO))){
		printf("delete record:%d\n",ret);
	}else{
		printf("delete record:%d\n",ret);
		goto SHOW;
	}
	//信息表----更新记录
	printf("update info:%d\n",record_update(sqt, STORAGE1INFO_NO));
	
	//出库表----插入记录
	strcpy(sqt->tables[STORAGE1OUT_NO]->fields[STORAGE1OUT_no].value, anamsg->goods_info);
	strcpy(sqt->tables[STORAGE1OUT_NO]->fields[STORAGE1OUT_name].value, "货物1");
	get_real_time(sqt->tables[STORAGE1OUT_NO]->fields[STORAGE1OUT_time].value);
	printf("insert 1out:%d\n",record_insert(sqt, STORAGE1OUT_NO));
	
SHOW:
#if 1
	/* 查询记录 */
	//用于检索的字段的 flag域 可设为 RETRIEVEAND/RETRIEVEOR/RETRIEVE/RETRIEVEALL;筛选值 写在对应 字段 的 value 域;
	puts("**********************STORAGE1INFO**********************");
	sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1IN_name].flag = RETRIEVEALL;
	strcpy(sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1INFO_name].value, "货物1" );
	printf("find info:%d\n",record_query(sqt, STORAGE1INFO_NO, 0));

	puts("**********************STORAGE1IN**********************");
	sqt->tables[STORAGE1IN_NO]->fields[STORAGE1IN_name].flag = RETRIEVEALL;
	strcpy(sqt->tables[STORAGE1IN_NO]->fields[STORAGE1INFO_name].value, "货物1" );
	printf("find 1in:%d\n",record_query(sqt, STORAGE1IN_NO, 0));

	puts("**********************STORAGE1OUT**********************");
	sqt->tables[STORAGE1OUT_NO]->fields[STORAGE1OUT_name].flag = RETRIEVEALL;
	strcpy(sqt->tables[STORAGE1OUT_NO]->fields[STORAGE1OUT_name].value, "货物1" );
	printf("find 1out:%d\n",record_query(sqt, STORAGE1OUT_NO, 0));
	
	puts("**********************ENVI1****************************");
	sqt->tables[ENVI1_NO]->fields[ENVI1_temp].flag = RETRIEVEALL;
	printf("find envi1:%d\n",record_query(sqt, ENVI1_NO, 0));
#endif 
	return 0;
}

int handle_env(db_t *sqt, sendEnv *anamsg)
{
	char envi_temp[20];
	
	sprintf(envi_temp, "%d.%d",anamsg->temp[0], anamsg->temp[1]);
	strcpy(sqt->tables[ENVI1_NO]->fields[ENVI1_temp].value, envi_temp);
	sprintf(envi_temp, "%d.%d",anamsg->hum[0], anamsg->hum[1]);
	strcpy(sqt->tables[ENVI1_NO]->fields[ENVI1_hum].value, envi_temp);
	sprintf(envi_temp, "%d", anamsg->light);
	strcpy(sqt->tables[ENVI1_NO]->fields[ENVI1_light].value, envi_temp);
	sprintf(envi_temp, "%d", anamsg->x);
	strcpy(sqt->tables[ENVI1_NO]->fields[ENVI1_x].value, envi_temp);
	sprintf(envi_temp, "%d", anamsg->y);
	strcpy(sqt->tables[ENVI1_NO]->fields[ENVI1_y].value, envi_temp);
	sprintf(envi_temp, "%d", anamsg->z);
	strcpy(sqt->tables[ENVI1_NO]->fields[ENVI1_z].value, envi_temp);
	get_real_time(sqt->tables[ENVI1_NO]->fields[ENVI1_time].value);
	printf("5插入记录:%d\n", record_insert(sqt, ENVI1_NO));
#if 1
	/* 查询记录 */
	puts("**********************STORAGE1INFO**********************");
	sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1IN_name].flag = RETRIEVEALL;
	strcpy(sqt->tables[STORAGE1INFO_NO]->fields[STORAGE1INFO_name].value, "货物1" );
	printf("find info:%d\n",record_query(sqt, STORAGE1INFO_NO, 0));

	puts("**********************STORAGE1IN**********************");
	sqt->tables[STORAGE1IN_NO]->fields[STORAGE1IN_name].flag = RETRIEVEALL;
	strcpy(sqt->tables[STORAGE1IN_NO]->fields[STORAGE1INFO_name].value, "货物1" );
	printf("find 1in:%d\n",record_query(sqt, STORAGE1IN_NO, 0));

	puts("**********************STORAGE1OUT**********************");
	sqt->tables[STORAGE1OUT_NO]->fields[STORAGE1OUT_name].flag = RETRIEVEALL;
	strcpy(sqt->tables[STORAGE1OUT_NO]->fields[STORAGE1OUT_name].value, "货物1" );
	printf("find 1out:%d\n",record_query(sqt, STORAGE1OUT_NO, 0));
	
	puts("**********************ENVI1****************************");
	sqt->tables[ENVI1_NO]->fields[ENVI1_temp].flag = RETRIEVEALL;
	printf("find envi1:%d\n",record_query(sqt, ENVI1_NO, 0));
	//用于检索的字段的 flag域 可设为 RETRIEVEAND/RETRIEVEOR/RETRIEVE/RETRIEVEALL;筛选值 写在对应 字段 的 value 域;
#endif 
	return 0;
}

